<?php

require_once WACT_ROOT . '/db/db.inc.php';
require_once APP_ROOT.'model/common/session.php';
require_once APP_ROOT.'model/common/site.php';


class Filter {
    function doFilter(&$vars){
    	$leagues=array('PSAL','CHSAA','LI Public Schools','LI Catholic Schools','Other');
    	$genders=array('boys','girls');
    	if($vars['published']==1) {
    		if(isset($vars['status']))
    			$status='Published (shown)';
    		else $status='Published (waiting)';
    	} else if($vars['denied']==1)
    		$status='Denied';
    		else $status='Pending';
    	$vars['status']=$status;
    	if(isset($vars['league'])) $vars['league']=$leagues[$vars['league']];
    	if(isset($vars['league_gender'])) $vars['league_gender']=$genders[$vars['league_gender']];
    	if(isset($vars['premium'])&&$vars['premium']==1){
    		$vars['prem']='1';
    	}
    }
}

class Team {

    function &getUserTeamsList(&$Pager,$user_id,$option=null) {
    	$site_id = Site::getSiteID();
    	$isGlobal = Site::IsGlobal();
    	$query="SELECT T.*,C.name as \"category1\",A.name as \"category2\", if(T.date_added<=now(),'true',NULL) as \"status\" ";
    	$query.="  FROM ";
    	$query.=" (sport_team T left outer join sport_categories C on T.category_id= C.category_id) ";
    	$query.=" left outer join sport_categories A on category2_id=A.category_id ";
    	$query.=" where user_id=".DBC::makeLiteral($user_id);
    	if($option!=null) $query.=" and T.league=".DBC::makeLiteral($option);
        $query.=" and (T.site_id=".DBC::makeLiteral($site_id);
        if ($isGlobal) $query.=" or 1=1) ";
    	else $query.=") ";
    	error_log("[SQL:] $query");
       	return DBC::NewPagedRecordSet($query,$Pager,new Filter());
    }

    function &getLeaguesList($none=false){

    	if($none){
    		$leagues=array('(ALL)','PSAL','CHSAA','LI Public Schools','LI Catholic Schools','Other');
    	} else {
			$leagues=array('PSAL','CHSAA','LI Public Schools','LI Catholic Schools','Other');
    	}

    	return $leagues;
    }

    function &getLeagueName($id) {
     	$leagues =& Team::getLeaguesList();
     	return $leagues[$id];
    }


    function &getGenders(){

		$genders=array(0=>'BOYS',1=>'GIRLS');
    	return $genders;
    }

    function &getGenderName($id) {
     	$genders =& Team::getGenders();
     	return $genders[$id];
    }



    function getTeamsCount($user_id) {
    	$site_id = Site::getSiteID();
    	$isGlobal = Site::IsGlobal();
    	$sql =  "Select count(*) from sport_team
    	where user_id=".DBC::makeLiteral($user_id)."
        and (site_id = ".DBC::makeLiteral($site_id);
        if ($isGlobal) $sql.=" or 1=1) ";
    	else $sql.=") ";
    	return DBC::getOneValue($sql);
    }

    function getNewTeamsCount() {
        $site_id = Site::getSiteID();
        $isGlobal = false;
        $sql = "Select count(*) from sport_team
    	where (published = 0 or published is null)
    	and (denied=0 or denied is null)
    	and (site_id = ".DBC::makeLiteral($site_id);
        if ($isGlobal) $sql.=" or 1=1) ";
    	else $sql.=") ";
    	return DBC::getOneValue($sql);
    }

  	function delete($id) {
        DBC::execute("DELETE FROM sport_team WHERE team_id=". DBC::makeLiteral($id));
    }

    function insertTeam(&$data) {
        $site_id = Site::getSiteID();
    	$data->set('site_id',$site_id);
        $Record =& DBC::NewRecord($data);
        return $Record->insertId('sport_team',
            array('user_id', 'category_id','category2_id', 'date_added', 'league_gender', 'league',
            'name', 'colors',
            'school_name', 'school_address', 'school_city', 'school_state', 'school_zip',
            'coach_name', 'coach_phone', 'coach_info',
            'top_players', 'players_in', 'players_out',
            'schedule', 'other',
            'date_show', 'published','site_id'),
            'team_id');

    }

    function denyTeam($id) {
        $data = new DataSpace();
        $data->set('denied',1);
        $data->set('published',0);
    	$Record =& DBC::NewRecord($data);
        $Record->update('sport_team',
            array('denied', 'published'),
            "team_id=". DBC::makeLiteral($id));
    }

    function publishTeam($id) {
        $data = new DataSpace();
        $data->set('denied',0);
        $data->set('published',1);
    	$Record =& DBC::NewRecord($data);
        $Record->update('sport_team',
            array('denied', 'published'),
            "team_id=". DBC::makeLiteral($id));
    }

    // get the next new team
    function &getNextNewTeam($current_team_id) {
    	$site_id = Site::getSiteID();
        $sql = "select A.* from sport_team A, sport_team A2
        where A.team_id <> A2.team_id
        and A2.team_id = ".DBC::makeLiteral($current_team_id)."
        and A.date_added <= A2.date_added
        and (A.published = 0  or  A.published is null)
        and A.site_id = ".DBC::makeLiteral($site_id)."
        and A2.site_id = ".DBC::makeLiteral($site_id)."
        order by A.date_added desc limit 1";
        return DBC::FindRecord($sql);
    }

    function &getRecord($id) {
        return DBC::FindRecord("SELECT U.*, T.*, C1.name as category1, C2.name as category2
        FROM sport_team T
        left outer join sport_users U on T.user_id = U.user_id
        left outer join sport_categories C1 on T.category_id = C1.category_id
     	left outer join sport_categories C2 on T.category2_id = C2.category_id
        WHERE T.team_id = ". DBC::makeLiteral($id));
    }

    function getPublicURLByID($id) {
        $url = 'teams/details.htm?id='.$id;
    	if (APP_NAME!='.') $url = '/'.APP_NAME.'/'.$url;
    	else  $url = '/'.$url;
    	return $url;
    	//return '/'.APP_NAME.'/'.'pub.team.php?team_id='.$id;
    }

    function getIDByName($name) {
     	$rec =& DBC::FindRecord("SELECT team_id
     	from sport_team A where A.name = ".DBC::makeLiteral($name)."");
     	if ($rec) return $rec->get('team_id');
     	else return 0;
    }

     function getPublicURLByName($name) {
     		$id = Team::getIDByName($name);
     		$url = Team::getPublicURLByID($id);
     		return $url;
     }

    function updateTeam($id, &$data) {
        $site_id = Site::getSiteID();
    	$data->set('site_id',$site_id);
        $Record =& DBC::NewRecord($data);
        $Record->update('sport_team',
            array('category_id','category2_id', 'league_gender', 'league',
            'name', 'colors',
            'school_name', 'school_address', 'school_city', 'school_state', 'school_zip',
            'coach_name', 'coach_phone', 'coach_info',
            'top_players', 'players_in', 'players_out',
            'schedule', 'other',
            'date_show', 'published','site_id'),
            "team_id=". DBC::makeLiteral($id));
    }

    function &getTeamsList(&$Pager, &$request, &$responseModel,$public=false) {
        $site_id = Site::getSiteID();
        $isGlobal = Site::IsGlobal();
        //restore filter from session
     	Session::loadResponseModel($responseModel,'filter.teams');

     	$sql = "select T.*, U.*, C1.name as category1, C2.name as category2
     	from sport_team T
     	left outer join sport_users U on T.user_id = U.user_id
     	left outer join sport_categories C1 on T.category_id = C1.category_id
     	left outer join sport_categories C2 on T.category2_id = C2.category_id
     	where 1=1 and T.published=1 ".Team::getTeamsFilter($request, $responseModel);
     	if($public) $sql.=" and T.date_added<=now() ";
        $sql.=" and (T.site_id = ".DBC::makeLiteral($site_id);
        if ($isGlobal) $sql.=" or 1=1) ";
    	else $sql.=") ";
     	$sql.=" order by ".Team::getTeamsOrder($request, $responseModel);
     	//print $sql;
     	error_log("[sql]:".$sql);
     	if($public) return DBC::NewPagedRecordSet($sql, $Pager, new Filter());
        return DBC::NewPagedRecordSet($sql, $Pager);


     }

     function &getNewTeamsList(&$Pager, &$request, &$responseModel) {
        $site_id = Site::getSiteID();
        $sql = "select T.*, U.*, C1.name as category1, C2.name as category2
     	from sport_team T
     	left outer join sport_users U on T.user_id = U.user_id
     	left outer join sport_categories C1 on T.category_id = C1.category_id
     	left outer join sport_categories C2 on T.category2_id = C2.category_id
        where 1=1 and (T.published=0 or T.published is null)
        and T.site_id = ".DBC::makeLiteral($site_id)."
     	order by T.date_added desc ";
     	return DBC::NewPagedRecordSet($sql, $Pager);

      }

     function getTeamsFilter(&$request, &$responseModel) {
      	$where = '';

      	$filter_gender = $responseModel->get('filter_gender');
        $gender_id = $responseModel->get('gender');

      	if ($filter_gender)  $where .= ' and T.league_gender = '.DBC::makeLiteral($gender_id);


        $filter_league_id = $responseModel->get('filter_league_id');
        $league_id = $responseModel->get('league_id');
      	if ($filter_league_id)  $where .= ' and T.league = '.DBC::makeLiteral($league_id);


        $filter_category1 = $responseModel->get('filter_category1');
        $category1 = $responseModel->get('category1');
        if ($filter_category1)  $where .= ' and T.category_id = '.DBC::makeLiteral($category1);

        $filter_category2 = $responseModel->get('filter_category2');
        $category2 = $responseModel->get('category2');
        if ($filter_category2)  $where .= ' and T.category2_id = '.DBC::makeLiteral($category2);

        return $where;
      }

      function getTeamsOrder(&$request, &$responseModel) {
      	$sortby = 'T.date_added';
      	$sortby = $responseModel->get('sort');
      	if ($sortby) return 'T.'.$sortby;
      	else return 'T.date_added';
      }





}

?>
